CREATE PROCEDURE [dbo].[eod_corporate_actions_api]
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	-- 1. Step
	-- Make sure there are no duplicate entries in table (or merge will fail)
    WITH source AS (
		SELECT	  coa_sym_cd
				, coa_exc_cd
				, coa_action_desc
				, coa_action_dt
				, rn = ROW_NUMBER() OVER (PARTITION BY    coa_sym_cd
														, coa_exc_cd
														, coa_action_desc
														, coa_action_dt 
										   ORDER BY		coa_action_dt DESC)
		FROM	raw.eod_corporate_actions
	)
	DELETE FROM source WHERE rn > 1;

	-- 2. Step
	-- Merge data Symbol into into final table
	MERGE INTO	fin.eod_corporate_actions WITH (HOLDLOCK) AS target
	USING		(SELECT	  coa_action_dt
						, sym_id
						, cat_id
						, coa_amount
				FROM	raw.eod_corporate_actions
						INNER JOIN	fin.eod_exchanges 
									ON (coa_exc_cd = exc_cd)
						INNER JOIN	fin.eod_symbols
									ON (	coa_sym_cd = sym_cd
										AND	exc_id = sym_exc_id)
						INNER JOIN fin.com_categories
									ON (coa_action_desc = cat_desc)) AS source
	ON				target.coa_sym_id = source.sym_id
				AND	target.coa_cat_id = source.cat_id
				AND target.coa_action_dt = source.coa_action_dt
	-- 2a: Existing symbol -> update data
	WHEN MATCHED THEN
		UPDATE SET	  target.coa_amount = source.coa_amount
	-- 2b: New symbol -> insert data
	WHEN NOT MATCHED BY TARGET THEN
		INSERT	( coa_sym_id
				, coa_cat_id
				, coa_action_dt
				, coa_amount )
		VALUES	( sym_id
				, cat_id
				, coa_action_dt
				, coa_amount );

	-- 3. Update Quotes to refelect corporate actions
	DECLARE @cur				AS CURSOR;
	DECLARE @qot_sym_id			AS INT;
	DECLARE @qot_trade_dt		AS DATE;
	DECLARE @qot_adj_factor		AS DECIMAL(18,6);
	DECLARE @cat_cd				AS VARCHAR(25);
	DECLARE @coa_id				AS INT;
	DECLARE @coa_amount			AS DECIMAL(18,6);
	DECLARE @prev_trade_dt		AS DATE;
	DECLARE @prev_adj_factor	AS DECIMAL(18,6);
	DECLARE @prev_close			AS DECIMAL(18,6);

	SET @cur = CURSOR FOR
	SELECT * 
	FROM (	SELECT		  qot_sym_id
						, qot_trade_dt
						, qot_adj_factor
						, cat_cd
						, coa_id
						, coa_amount 
						, LEAD(qot_trade_dt, 1) OVER (ORDER BY qot_sym_id ASC, qot_trade_dt DESC)	AS prev_trade_dt
						, LEAD(qot_adj_factor, 1) OVER (ORDER BY qot_sym_id ASC, qot_trade_dt DESC) AS prev_adj_factor
						, LEAD(qot_close, 1) OVER (ORDER BY qot_sym_id ASC, qot_trade_dt DESC)		AS prev_close
			FROM		fin.eod_quotes 
						LEFT OUTER JOIN fin.eod_corporate_actions ON (qot_sym_id = coa_sym_id and qot_trade_dt = coa_action_dt)
						LEFT OUTER JOIN fin.com_categories ON (coa_cat_id = cat_id)
			WHERE			qot_cat_id = 7 
			) t
	WHERE		t.coa_id is not null
			AND qot_adj_factor is null
	ORDER BY	qot_sym_id ASC
	          , qot_trade_dt DESC;

	OPEN @cur;
	FETCH NEXT FROM @cur INTO	  @qot_sym_id
								, @qot_trade_dt
								, @qot_adj_factor
								, @cat_cd
								, @coa_id
								, @coa_amount
								, @prev_trade_dt
								, @prev_adj_factor
								, @prev_close;

	WHILE @@FETCH_STATUS = 0
	BEGIN
		
		UPDATE	fin.eod_quotes
		SET		qot_adj_factor = 1
		WHERE		qot_sym_id = @qot_sym_id
				AND qot_cat_id = 7
				AND qot_trade_dt = @qot_trade_dt
				AND qot_adj_factor IS NULL;

		IF @cat_cd = 'DIV'
		BEGIN
			UPDATE	fin.eod_quotes 
			SET		qot_adj_factor = (1.0-@coa_amount/@prev_close) * coalesce(qot_adj_factor, 1.0)
			WHERE		qot_sym_id = @qot_sym_id
					AND qot_trade_dt < @qot_trade_dt
					AND qot_cat_id = 7;
		END

		IF @cat_cd = 'SPT'
		BEGIN
			UPDATE	fin.eod_quotes 
			SET		qot_adj_factor = coalesce(qot_adj_factor, 1.0) * 1/@coa_amount
			WHERE		qot_sym_id = @qot_sym_id
					AND qot_trade_dt < @qot_trade_dt
					AND qot_cat_id = 7;
		END
	

		FETCH NEXT FROM @cur INTO	  @qot_sym_id
									, @qot_trade_dt
									, @qot_adj_factor
									, @cat_cd
									, @coa_id
									, @coa_amount
									, @prev_trade_dt
									, @prev_adj_factor
									, @prev_close;
	END;

	CLOSE @cur;
	DEALLOCATE @cur;

END